/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package limousine.dbservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.DataSource;
import limousine.util.Func;
import limousine.vo.Lookup;

/**
 *
 * @author Tailer
 */
public class LookupService {

    private DataSource ds = null;
    private Func func = new Func();

    public DataSource getDs() {
        return ds;
    }

    public void setDs(DataSource ds) {
        this.ds = ds;
    }

    public Lookup getByTableId(String tableId) {
        StringBuffer sql = new StringBuffer();
        sql.append("select * From LOOKUP where TABLE_ID = '" + tableId.trim() + "' ");
        Lookup o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Lookup();
                o.setTableId(rs.getInt("TABLE_ID"));
                o.setLookupId(rs.getString("LOOKUP_ID"));
                o.setLookupName(rs.getString("LOOKUP_NAME"));
                o.setLookupDescription(rs.getString("LOOKUP_DESCRIPTION"));
                o.setLookupGroupId(rs.getString("LOOKUP_GROUP_ID"));
                o.setLookupOrder(rs.getInt("LOOKUP_ORDER"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public boolean insert(Lookup o) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO LOOKUP(TABLE_ID,LOOKUP_ID,LOOKUP_NAME,LOOKUP_DESCRIPTION,LOOKUP_GROUP_ID,LOOKUP_ORDER) ");
        sql.append("VALUES (TABLE_ID,?,?,?,?,?)");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());            
            stmt.setString(1, o.getLookupId());
            stmt.setString(2, o.getLookupName());
            stmt.setString(3, o.getLookupDescription());
            stmt.setString(4, o.getLookupGroupId());
            stmt.setInt(5, o.getLookupOrder());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean update(Lookup o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE LOOKUP ");
        sql.append("SET LOOKUP_ID=?,LOOKUP_NAME=?,LOOKUP_DESCRIPTION=?,LOOKUP_GROUP_ID=?,LOOKUP_ORDER=? ");
        sql.append("where TABLE_ID='" + o.getTableId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getLookupId());
            stmt.setString(2, o.getLookupName());
            stmt.setString(3, o.getLookupDescription());
            stmt.setString(4, o.getLookupGroupId());
            stmt.setInt(5, o.getLookupOrder());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteByTableId(String tableId) {
        StringBuffer sql = new StringBuffer();
        sql.append("delete from LOOKUP where TABLE_ID = '" + tableId.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}
